﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Reflection;
using AddressBook.Entity;

namespace AddressBook.DAO
{
    public class VersionNoDAO
    {
        public int InsertData(VersionNo model)
        {
            try
            {
                StringBuilder sbColumn = new StringBuilder("insert into VersionNo(");
                StringBuilder sbValues = new StringBuilder(" )values(");
		PropertyInfo[] properties = model.GetType().GetProperties();
		SqlParameter[] parameters = new SqlParameter[properties.Length - 1];
		int i = 0;
				
                foreach (System.Reflection.PropertyInfo info in model.GetType().GetProperties())
                {
					if(info.Name == "VersionID" ){ continue; }
                    object value = info.GetValue(model, null);
					sbColumn.AppendFormat("{0},", info.Name);
					sbValues.AppendFormat("@{0},", info.Name);
                    if (value != null)
                    {
                        parameters[i] = new SqlParameter("@" + info.Name, value);
                    }
                    else
                    {
                        parameters[i] = new SqlParameter("@" + info.Name, DBNull.Value);
                    }
                    i++ ;
                }
                string cmd = sbColumn.Remove(sbColumn.Length - 1, 1).Append(sbValues.Remove(sbValues.Length - 1, 1).ToString()).ToString() + ")";
                return Globle.ExecuteNonQuery(cmd,CommandType.Text,parameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int UpdateData(VersionNo model)
        {
            try
            {
                StringBuilder sb = new StringBuilder(" where 1=1 ");
                sb.Append(" and VersionID=@VersionID");
                return UpdateData(model, sb.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int UpdateData(VersionNo model, string whereStr)
        {
            try
            {
                StringBuilder sbColumn = new StringBuilder("Update VersionNo set ");
                PropertyInfo[] properties = model.GetType().GetProperties();
                SqlParameter[] parameters = new SqlParameter[properties.Length - 1];
                
                parameters[0] = new SqlParameter("@VersionID", model.VersionID);
                int i = 1;
                foreach (PropertyInfo info in properties)
                {
					if(info.Name == "VersionID" ){ continue; }
                    object value = info.GetValue(model, null);
                    sbColumn.AppendFormat("{0}=@{0},", info.Name);
                    if (value != null)
                    {
						parameters[i] = new SqlParameter("@" + info.Name, value);
                    }
                    else
                    {
                        parameters[i] = new SqlParameter("@" + info.Name, DBNull.Value);
                    }
					i++ ;
                }
                string cmd = sbColumn.Remove(sbColumn.Length - 1, 1).Append(whereStr).ToString();
                return Globle.ExecuteNonQuery(cmd,CommandType.Text,parameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int DeleteData(string whereStr)
        {
            try
            {
		return Globle.ExecuteNonQuery("delete from VersionNo " + whereStr);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int DeleteData(VersionNo model)
        {
            try
            {
                StringBuilder sb = new StringBuilder(" where 1=1 ");
                sb.Append(" and VersionID=@VersionID");
                SqlParameter[] parameters = new SqlParameter[1];
                parameters[0] = new SqlParameter("@VersionID", model.VersionID);
                return DeleteData(sb.ToString(), parameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        
        public int DeleteData(string whereStr, SqlParameter[] parameters)
        {
            try
            {
		return Globle.ExecuteNonQuery("delete from VersionNo " + whereStr,CommandType.Text,parameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
                
        public int DeleteByKey(int versionid)
        {
            try
            {
		SqlParameter[] parameters = new SqlParameter[1];
                parameters[0] = new SqlParameter("@VersionID", versionid);
		return DeleteData("where VersionID=@VersionID",parameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        
        public VersionNo GetInfoByKey(int versionid)
        {
            try
            {
	        SqlParameter[] parameters = new SqlParameter[1];
                parameters[0] = new SqlParameter("@VersionID", versionid);
                List<VersionNo> models = Globle.FillModel<VersionNo>(GetInfo("*", "where VersionID=@VersionID", "", "",parameters));
                if (models.Count > 0)
                    return models[0];
                else
                    return null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public VersionNo GetInfo(string whereStr)
        {
            try
            {
                List<VersionNo> models = Globle.FillModel<VersionNo>(GetInfo(" top 1 *", whereStr, "", ""));
                if (models.Count > 0)
                    return models[0];
                else
                    return null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public List<VersionNo> GetInfos(string columns, string whereStr, string orderby, string groupby)
        {
            try
            {
                List<VersionNo> models = Globle.FillModel<VersionNo>(GetInfo(columns, whereStr, orderby, groupby));
                return models;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetInfo(string columns, string whereStr, string orderby, string groupby)
        {
            try
            {
                string sqlstring = string.Format("select {0} from VersionNo with(nolock) {1} {2} {3}", columns, whereStr, orderby, groupby);
                return Globle.GetSQLDataTable(sqlstring);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        
        public DataTable GetInfo(string columns, string whereStr, string orderby, string groupby, SqlParameter[] parameters)
        {
            try
            {
                string sqlstring = string.Format("select {0} from VersionNo with(nolock) {1} {2} {3}", columns, whereStr, orderby, groupby);
                return Globle.GetSQLDataTable(sqlstring,CommandType.Text,parameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

    }
}
